Database reference guide

HOME

WHERE Clause

Summary

The following topics are covered in this section:

Basic Where Clause
Operators
NOT
Null Records
Non-Null Records
Multiple Where Clauses
IN Statement
BETWEEN Statement
XBETWEEN Statement

Basic WHERE Clause

The WHERE clause is used to specify the records that should be included in the Domain, so that:

SELECT *

FROM Customer

WHERE Age > 32;

createS a Domain that only includes customers older than 32.

Record	Forename	Surname	Sex	Age	Town
0	HILDA	SMITH	F	35	BRISTOL
1	JOHN	SMITH	M	42	CARDIFF
5	DOUG	SMALL	M	35	CHESTER
6	IAN	WRIGHT	M	68	BATH
7	SAMANTHA	NEWTON		35	CHESTER
8	FLORENCE	LANE	F	61	CARDIFF

The simplest WHERE clause is:

WHERE <Column Name> <Operator> <Data>

Operators

The basic operators that can be used are:

= or EQ - record in column is equal to data.

< or LT - record in column is less than data.

> or GT - record in column is greater than data.

<= or LE - record in column is less than or equal to data.

>= or GE - record in column is greater than or equal to data.

<> or NE - record in column is not equal to data.

For example:

SELECT Count(*)

FROM Customer

WHERE Sex = “F”;

will include only females and

SELECT Count(*)

FROM Customer

WHERE Age = 32;

will include only people aged 32.

NOT

It is possible to find the converse of the records included in the WHERE clause by including the NOT statement before the column name, so that:

SELECT *

FROM Customer

WHERE NOT Sex = “F”;

returns

Record	Forename	Surname	Sex	Age	Town
1	JOHN	SMITH	M	42	CARDIFF
2	PAUL	JONES	M	21	CARDIFF
5	DOUG	SMALL	M	35	CHESTER
6	IAN	WRIGHT	M	68	BATH
9	DAVID	ROGERS	M	23	BATH

This creates a Domain that includes all customers that are not female. However, the records of unknown or Null value will not be included in the Domain.

NULL Records

To find records that are unknown or null, the IS NULL statement must be used:

SELECT *

FROM Customer

WHERE Sex IS NULL;

Record	Forename	Surname	Sex	Age	Town
7	SAMMY	NEWTON		35	CHESTER

Non-Null Records

To find all the records that are not Null the following is used:

SELECT *

FROM Customer

WHERE NOT Sex IS NULL;

Multiple WHERE clauses

The statements within the WHERE clause can be logically linked to form more complex domains, using OR and AND to determine how each element is to be combined, so that

SELECT *

FROM Customer

WHERE Sex = “F” AND Age = 21;

forms a Domain containing only females aged 21:

Record	Forename	Surname	Sex	Age	Town
4	ALISON	SMYTH	F	21	BRISTOL
10	HELEN	LAKER	F	21	BATH

The following statement forms a Domain containing all females regardless of age, together with all people aged 21 regardless of gender.

SELECT *

FROM Customer

WHERE Sex = “F” OR Age = 21;

Record	Forename	Surname	Sex	Age	Town
0	HILDA	SMITH	F	35	BRISTOL
2	PAUL	JONES	M	21	CARDIFF
3	SARAH	BROWN	F	22	CHESTER
4	ALISON	SMYTH	F	21	BRISTOL
8	FLORENCE	LANE	F	61	CARDIFF
10	HELEN	LAKER	F	21	BATH

You can link as many statements as you need,as demonstrated below:

SELECT *

FROM Customer

WHERE Sex = “F” AND Age = 21

AND Town = “BRISTOL”;

This will produce a Domain containing all females aged 21 living in Bristol.

Record	Forename	Surname	Sex	Age	Town
4	ALISON	SMYTH	F	21	BRISTOL

You can use the OR operator to obtain multiple values from the same column:

SELECT *

FROM Customer

WHERE Sex = “F”

AND Age = 21 OR Age = 22 OR Age = 23;

The Domain formed will include females aged 21 together with anyone aged 22 or anyone aged 23.

Record	Forename	Surname	Sex	Age	Town
3	SARAH	BROWN	F	22	CHESTER
4	ALISON	SMYTH	F	21	BRISTOL
9	DAVID	ROGERS	M	23	BATH
10	HELEN	LAKER	F	21	BATH

If the required Domain is to contain only females aged 21 to 23 then parentheses should be used to isolate the statements in the clause, for example:

SELECT *

FROM Customer

WHERE Sex = “F”

AND( Age = 21 OR Age = 22 OR Age = 23);

Record	Forename	Surname	Sex	Age	Town
3	SARAH	BROWN	F	22	CHESTER
4	ALISON	SMYTH	F	21	BRISTOL
10	HELEN	LAKER	F	21	BATH

IN Statement

When a WHERE clause, as in the previous example, is to include many values for a single column linked by OR, there are two further operators that can be used to simplify (and speed up) the query. The first of these operators is IN.

IN is followed by a list of values to be included in the Domain for that specific column:

SELECT Count(*)
FROM Customer
WHERE Sex = “F” AND Age IN(21,22,23);

The list following the IN operator can contain one or many elements, IN lists for text fields should be contained in quotes (""), for example:

WHERE Surname IN ("SMITH","JONES","BROWN");

Record	Forename	Surname	Sex	Age	Town
0	HILDA	SMITH	F	35	BRISTOL
1	JOHN	SMITH	M	42	CARDIFF
2	PAUL	JONES	M	21	CARDIFF
3	SARAH	BROWN	F	22	CHESTER

Note that:

WHERE Surname IN ("SMITH");

Is exactly the same as:

WHERE Surname = "SMITH";

BETWEEN Statement

The BETWEEN operator specifies the upper and lower inclusive values of a column's records.

SELECT Count(*)

FROM Customer

WHERE Sex = “F” AND Age BETWEEN 20 and 24;

This can also be written as:

SELECT Count(*)

FROM Customer

WHERE Sex = “F” AND Age BETWEEN 20,24;

The BETWEEN operator can be used to create more efficient NSQL, so that:

SELECT Count(*)

FROM Customer

WHERE Age >= 20 AND Age <= 30;

Could be written:

SELECT Count(*)

FROM Customer

WHERE Age BETWEEN 20 and 30;

XBETWEEN Statement

XBETWEEN is an alternative form of BETWEEN. This operator is similar to BETWEEN except that it is exclusive. The result set will not include any records that are equal to the values that specify the range.

For example:

SELECT Count(*)

FROM Customer

WHERE Age > 20 AND Age < 30;

Could be written:

SELECT Count(*)

FROM Customer

WHERE Age XBETWEEN 20 and 30;

	Online & Instructor-Led Courses \| Training Videos \| Webinar Recordings
	© Alterian. All Rights Reserved. \| Privacy Policy \| Legal Notice